package nc.hrpbm.cg.servlet;

import java.io.BufferedReader;
import java.io.IOException;
import java.io.PrintWriter;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.UndeclaredThrowableException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import nc.itf.hrpbm.sjxm.sjxmh.ISjxmHVOService;
import nc.jdbc.framework.processor.ColumnListProcessor;
import nc.vo.pub.CircularlyAccessibleValueObject;
import nc.vo.pub.VOStatus;
import nc.vo.pub.lang.UFDateTime;
import nc.vo.sjxm.sjxm.AggSjxmVO;
import nc.vo.sjxm.sjxm.SjxmBVO;
import nc.vo.sjxm.sjxm.SjxmHVO;
import org.hsqldb.lib.StringUtil;

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.google.gson.Gson;

import cn.hutool.core.collection.CollectionUtil;
import cn.hutool.core.util.ArrayUtil;
import nc.bs.dao.BaseDAO;
import nc.bs.framework.adaptor.IHttpServletAdaptor;
import nc.bs.framework.common.InvocationInfoProxy;
import nc.bs.framework.common.NCLocator;
import nc.bs.framework.server.ISecurityTokenCallback;
import nc.bs.util.DefdocUtils;
import nc.itf.hrpbm.fourcgxm.fourcgxm.ICGProjectToNCProjectService;
import nc.itf.hrpbm.fourcgxm.fourcgxm.IFourcgxmVOService;
import nc.jdbc.framework.generator.IdGenerator;
import nc.jdbc.framework.processor.ColumnProcessor;
import nc.jdbc.framework.processor.MapListProcessor;
import nc.vo.fourcgxm.fourcgxm.AggFourcgxmVO;
import nc.vo.fourcgxm.fourcgxm.FourcgxmVO;
import nc.vo.hrpbm.budgetprocureproject.BugetProcureProjectDataRequest;
import nc.vo.hrpbm.budgetprocureproject.BugetProcureProjectHVO;
import nc.vo.hrpbm.budgetprocureproject.BusinessCodeEnumerate;
import nc.vo.pub.BusinessException;
import ncc.bs.utils.InputLogger;
import ncc.bs.utils.ResultMessage;

/**
 * @description: 采购系统同步到HOP系统接口
 * @author: lz
 **/

// http://192.168.104.123:8090/service/thirdInterface
@SuppressWarnings("restriction")
public class CgToHopService extends HttpServlet implements IHttpServletAdaptor {
    private static final String logName = "cglog";
    public BaseDAO baseDAO;
    private ICGProjectToNCProjectService toNCProjectService;
    private ISjxmHVOService sjxmHVOService;

    @Override
    public void doAction(HttpServletRequest req, HttpServletResponse resp) {
        InputLogger.info(logName, "-----begin-----第三方系统请求四级采购项目");
        JSONObject paramJson = null;
        StringBuilder error = new StringBuilder();
        String message = null;
        PrintWriter pw = null;
        BufferedReader reader = null;
        Gson gson = new Gson();
        try {
            resp.setContentType("application/json");
            resp.setCharacterEncoding("UTF-8");
            req.setCharacterEncoding("UTF-8");
            pw = resp.getWriter();
            StringBuilder sb = new StringBuilder();
            reader = req.getReader();
            String line;
            while ((line = reader.readLine()) != null) {
                sb.append(line);
            }
            if (sb.toString().length() < 1) {
                message = "请求参数不允许为空,请检查";
                InputLogger.info(logName, "-----" + message);
                pw.println(ResultMessage.error(message));
                return;
            }
            NCLocator.getInstance().lookup(ISecurityTokenCallback.class).token("NCSystem".getBytes(), "pfxx".getBytes());
            InvocationInfoProxy.getInstance().setUserDataSource("design");
            String paramStr = sb.toString();
            paramJson = JSONObject.parseObject(paramStr);
            String businessCode = paramJson.getString("businessCode");
            InputLogger.info(logName, "-----三方系统为: 【" + paramJson.getString("sender") + "】,,请求参数为:【" + paramJson + "】 ");
            // 参数转换对象
            List<BugetProcureProjectHVO> list = paramsToVo(paramJson, error);
            if(error.length() > 0){
                throw new BusinessException(error.toString());
            }
            if (businessCode.equals(BusinessCodeEnumerate.INSERT.getCode())) {
                // 生成NC四级采购项目,回写信息
                AggSjxmVO[] aggvos = this.getToNCProjectService().saveCGProjectToFourCGXMVOs(list);
                list = this.getToNCProjectService().writeCGProject(aggvos, list);
                // 新增数据
                getBaseDAO().insertVOList(list);// 保存中间库
                message = error.length() > 0 ? "以下单据新增失败:【" + error + "】,其余单据新增成功" : "新增成功";
                paramJson.put("state", 200);
                paramJson.put("isSucccess", true);
                paramJson.put("error", message);
                InputLogger.info(logName, "-----" + message + "--,响应参数:【" + paramJson + "】");
            } else if (businessCode.equals(BusinessCodeEnumerate.QUERY.getCode())
                    || businessCode.equals(BusinessCodeEnumerate.QUERYTHREE.getCode())) {
                // 查询
                String sql = buildSQL(list, businessCode);
                Object obj = getBaseDAO().executeQuery(sql, new MapListProcessor());
                List<Map<String, Object>> retList = null;
                JSONArray resdata = new JSONArray();
                message = "查无此数据";
                if (null != obj && ArrayUtil.isAllNotEmpty(obj)) {
                    retList = (List<Map<String, Object>>) obj;
                    retList = this.getFundSource(retList, businessCode);
                    String res = gson.toJsonTree(retList).getAsJsonArray().toString();
                    resdata = JSONArray.parseArray(res);
                    message = "查询成功";
                }
                paramJson.put("data", resdata);
                paramJson.put("state", 200);
                paramJson.put("isSucccess", true);
                paramJson.put("error", message);
                InputLogger.info(logName, "-----" + message + "--响应参数:【" + paramJson + "】");
            } else if (businessCode.equals(BusinessCodeEnumerate.UPDATE.getCode())) {
                // 更新
                String[] names = new String[] {  "applydept", "pldycbks","fourstate",
                        "spec", "nnum", "price", "money", "modifier", "modifyobj",
                        "def1","def2","def3","def4","def5","def6","def7","def8","def9","def10" }; // 允许修改的字段
                List<AggSjxmVO> listvos = new ArrayList<AggSjxmVO>();
                JSONArray mess = new JSONArray();
                int state = 500;
                for(BugetProcureProjectHVO vo : list) {
                    JSONObject json = new JSONObject();
                    String pk_cgid = vo.getPk_cgid();
                    String q = "select hrpbm_sjxmh.pk_sjxm from hrpbm_sjxmh where hrpbm_sjxmh.pk_cgid = '"+pk_cgid+"'";
                    Object pk = this.getBaseDAO().executeQuery(q, new ColumnProcessor());
                    if(pk == null ) {
                        message = "三方单据号PK:"+pk_cgid+",在HRP中不存在\n";
                        throw new BusinessException(message);
                    }else {
                        AggSjxmVO aggvo = this.getSjxmHVOService().findAggSjxmVOByPk(pk.toString());
                        aggvo.getParent().setStatus(VOStatus.UPDATED);
                        SjxmHVO hvo = aggvo.getParentVO();
                        hvo.setAttributeValue("modifyobj", "CG");
                        for(String att : names ) {
                            Object value = vo.getAttributeValue(att);
                            if(value != null && !att.contains("def")) {
                                hvo.setAttributeValue(att, value);
                            }
                        }
                        SjxmBVO[] bvos = (SjxmBVO[])aggvo.getChildrenVO();
                        if(ArrayUtil.isAllNotEmpty(bvos)) {
                            String applyyear = vo.getApplyyear();
                            if( applyyear == null || "".equals(applyyear)) {
                                throw new BusinessException("申请/预算年度不允许为空 ");
                            }
                            for (SjxmBVO bvo : bvos) {
                                String ysyear = bvo.getYsyear();
                                if(applyyear.equals(ysyear)){
                                    bvo.setStatus(VOStatus.UPDATED);
                                    bvo.setAttributeValue("modifyobj", "CG");
                                    for(String att : names ) {
                                        Object value = vo.getAttributeValue(att);
                                        if(value != null && att.contains("def") && !"def1".equals(att)) {// bdef1
                                            bvo.setAttributeValue("b"+att, value);
                                        }else if(value != null && "fourstate".equals(att)) {
                                            bvo.setAttributeValue("fourstate",value);
                                        }
                                    }
                                }
                            }
                        }
                        json.put("pk_cgid", pk_cgid+"更新成功");
                        mess.add(json);
                        listvos.add(aggvo);
                    }
                }
                AggSjxmVO[] upaggvos = listvos.toArray(new AggSjxmVO[listvos.size()]);
                this.getSjxmHVOService().saveAggSjxmVO(upaggvos);
                state = 200;
                paramJson.put("state", state);
                paramJson.put("isSucccess", true);
                paramJson.put("data", mess);
                paramJson.put("error", message);
                InputLogger.info(logName, "-----" + message + ",响应参数:【" + paramJson + "】");
            } else if (businessCode.equals(BusinessCodeEnumerate.TERMINATION.getCode())) {
                // 终止
                if (CollectionUtil.isNotEmpty(list)) {
                    StringBuilder e = new StringBuilder();
                    for(int i = 0; i < list.size(); i++){
                        BugetProcureProjectHVO vo = list.get(i);
                        String pkCgid = vo.getPk_cgid();
                        String applydept = vo.getApplydept();
                        if((pkCgid == null || "".equals(pkCgid)) || applydept == null || "".equals(applydept)) {
                            e.append("行号: "+ (i+1) +",预算年度与采购系统ID 两者为必传字段！");
                        }
                        String qsql = "SELECT " +
                                " count(*)  " +
                                "FROM " +
                                " HRPBM_SJXMH h " +
                                "INNER JOIN HRPBM_SJXMB b ON b.pk_sjxm = h.pk_sjxm " +
                                "where h.dr = 0 and b.dr = 0 AND ( h.pk_cgid, b.ysyear ) IN ( ( '"+vo.getPk_cgid()+"', '"+vo.getApplydept()+"' )) ";
                        int count = (int)this.getBaseDAO().executeQuery(qsql, new ColumnProcessor());
                        if(count < 1){
                            e.append("行号: "+ (i +1) + ", 采购系统ID: ["+vo.getPk_cgid()+"], 预算年度["+vo.getApplydept()+"],不存在对应的四级项目, ");
                        }
                    }
                    if(e.length() > 0){
                        throw new BusinessException(e.toString());
                    }
                    StringBuilder sql = new StringBuilder();
                    sql.append("MERGE INTO HRPBM_SJXMB b USING HRPBM_SJXMH h " );
                    sql.append("ON ( b.pk_sjxm = h.pk_sjxm and (h.pk_cgid, b.ysyear) in (  ");
                    for (int i = 0; i < list.size(); i++) {
                        BugetProcureProjectHVO vo = list.get(i);
                        if(i == 0){
                            sql.append("('"+vo.getPk_cgid()+"','"+vo.getApplydept()+"')");
                        }else{
                            sql.append(", ('"+vo.getPk_cgid()+"','"+vo.getApplydept()+"')");
                        }
                    }
                    sql.append(") )");
                    sql.append("WHEN MATCHED THEN  ");
                    sql.append("UPDATE  " );
                    sql.append(" SET b.fourstate = 4 ");
                    getBaseDAO().executeUpdate(sql.toString());
                }

                message = "终止成功";
                paramJson.put("state", 200);
                paramJson.put("isSucccess", true);
                paramJson.put("error", message);
                InputLogger.info(logName, "-----" + message + ",响应参数:【" + paramJson + "】");
            } else if (businessCode.equals(BusinessCodeEnumerate.DELETE.getCode())) {
                // 删除（更新dr）
                JSONArray dataJson = paramJson.getJSONArray("data");
                if (!dataJson.isEmpty()) {
                    JSONObject pk_cgidObj = JSONObject.parseObject(dataJson.get(0).toString());
                    if (!pk_cgidObj.isEmpty()) {
                        JSONArray cgpk = pk_cgidObj.getJSONArray("pk_cgid");
                        if (cgpk != null && !cgpk.isEmpty()) {
                            String[] cgpks = cgpk.toArray(new String[cgpk.size()]);
                            List<AggSjxmVO> lvos = new ArrayList<>();
                            for (String pk : cgpks) {
                                String sql = "select hrpbm_sjxmh.pk_sjxm from hrpbm_sjxmh where hrpbm_sjxmh.pk_cgid = '"+pk+"'";
                                Object sjpk = this.getBaseDAO().executeQuery(sql, new ColumnProcessor());
                                if (sjpk != null) {
                                    AggSjxmVO aggvo = this.getSjxmHVOService().findAggSjxmVOByPk(sjpk.toString());
                                    aggvo.getParent().setStatus(VOStatus.DELETED);
                                    lvos.add(aggvo);
                                }else{
                                    throw new BusinessException("采购系统ID【"+pk+"】,在用友HRP中不存在对应的项目\n");
                                }
                            }
                            if (lvos != null && lvos.size() > 0) {
                                this.getSjxmHVOService().saveAggSjxmVO(lvos.toArray(new AggSjxmVO[lvos.size()]));
                            }
                        }else{
                            message = "请检查请求参数:";
                            throw new BusinessException(BusinessCodeEnumerate.DELETE.getCode(), message);
                        }
                    }
                }
                paramJson.put("state", 200);
                paramJson.put("isSucccess", true);
                paramJson.put("error", message);
                InputLogger.info(logName, "-----" + message + ",响应参数:【" + paramJson + "】");
            }

        } catch (Exception e) {
            if(e instanceof java.lang.reflect.InvocationTargetException) {
                error.append(  ((java.lang.reflect.InvocationTargetException)e).getTargetException().getMessage());
            }else if(e instanceof java.lang.reflect.UndeclaredThrowableException) {
                Throwable throwable = ((UndeclaredThrowableException)e).getUndeclaredThrowable();
                if(throwable instanceof java.lang.reflect.InvocationTargetException) {
                    Throwable targetException = ((InvocationTargetException)throwable).getTargetException();
                    error.append(targetException.getClass().getName()+" : "+targetException.getMessage());
                }
            }else {
                error.append(e.getMessage());
            }
            paramJson.put("error", error.toString());
            paramJson.put("state", 500);
            paramJson.put("isSucccess", false);
            InputLogger.error(logName, "-----接口异常,异常信息:【" + e.getMessage() + "】");
        } finally {
            if (reader != null) {
                try {
                    reader.close();
                } catch (IOException e) {
                    InputLogger.error(logName, "-----BufferedReader流关闭失败");
                }
            }
        }
        pw.println(paramJson);
        pw.flush();
        pw.close();
        InputLogger.info(logName, "-----end-----第三方系统请求四级采购项目");
    }

    /**
     * 获取请求oa参数
     *
     * @param paramJson 入参
     * @return
     * @throws BusinessException
     */
    private List<BugetProcureProjectHVO> paramsToVo(JSONObject paramJson, StringBuilder error)
            throws BusinessException {
        InputLogger.info(logName, "----begin--开始解析,三方系统: 【" + paramJson.getString("sender") + "】,请求参数 ");
        List<BugetProcureProjectHVO> list = new ArrayList<>();
        String sender = paramJson.getString("sender");
        String billType = paramJson.getString("billType");
        String businessCode = paramJson.getString("businessCode");
        if (StringUtil.isEmpty(businessCode)) {
            throw new BusinessException("请求参数中businessCode不允许为空!");
        }
        JSONArray dataJson = paramJson.getJSONArray("data");
        if (dataJson.isEmpty() && !businessCode.contains("Q")) {
            throw new BusinessException("请求参数中data不允许为空!");
        }
        if (businessCode.equals(BusinessCodeEnumerate.DELETE.getCode())) {
            // 这里不用转换对象，有其他逻辑做处理，返回空的list
            return list;
        }
        if (businessCode.equals(BusinessCodeEnumerate.QUERYTHREE.getCode())) {
            dataJson.forEach(o -> {
                JSONObject objJson = JSONObject.parseObject(JSON.toJSONString(o));
                if (!objJson.isEmpty()) {
                    String threeprono = objJson.getString("threeprono");// threeprono
                    String def1 = objJson.getString("pk_deptid");// def1
                    String applyyear = objJson.getString("year");// applyyear
                    BugetProcureProjectHVO vo = new BugetProcureProjectHVO();
                    vo.setThreeprono(threeprono);
                    vo.setDef1(def1);
                    vo.setApplyyear(applyyear);
                    list.add(vo);
                }
            });
        } else {
            List<BugetProcureProjectDataRequest> dataList = dataJson.toJavaList(BugetProcureProjectDataRequest.class);
            List<Map<String, Object>> lists = null;
            if (businessCode.equals(BusinessCodeEnumerate.INSERT.getCode())) {
                lists = DefdocUtils.getDefdoc("CGHRP_CG001");

            }
            for (BugetProcureProjectDataRequest o : dataList) {
                BugetProcureProjectHVO bugetProcureProjectHVO = new BugetProcureProjectHVO();
                if (businessCode.equals(BusinessCodeEnumerate.INSERT.getCode())) {
                    String pk_cgid = o.getPk_cgid();
                    Object obj = this.getBaseDAO().executeQuery(
                            "select pk_cgid from CG_TO_HOP_PROJECT where" + " dr = 0 and  pk_cgid = '" + pk_cgid + "'",
                            new ColumnProcessor());
                    if (obj != null) {
                        error.append("ID: " + pk_cgid + ",单据号: " + o.getCgbillcode() + " 已存在,不允许新增; ");
                        continue;
                    }
                    bugetProcureProjectHVO.setPk_hopid(NCLocator.getInstance().lookup(IdGenerator.class).generate());
                    bugetProcureProjectHVO.setDr(0);
                    if (lists != null) {
                        for (Map<String, Object> map : lists) {
                            if ("pk_group".equalsIgnoreCase(map.get("code").toString())) {
                                bugetProcureProjectHVO.setAttributeValue("pk_group", map.get("shortname"));
                            } else if ("pk_org".equalsIgnoreCase(map.get("code").toString())) {
                                bugetProcureProjectHVO.setAttributeValue("pk_org", map.get("shortname"));
                            } else if ("pk_org_v".equalsIgnoreCase(map.get("code").toString())) {
                                bugetProcureProjectHVO.setAttributeValue("pk_org_v", map.get("shortname"));
                            }
                        }
                    }
                }
                bugetProcureProjectHVO.setPk_cgid(o.getPk_cgid());
                bugetProcureProjectHVO.setCgbillcode(o.getCgbillcode());
                bugetProcureProjectHVO.setApplydept(o.getApplydept());
                bugetProcureProjectHVO.setPldycbks(o.getPldycbks());
                bugetProcureProjectHVO.setApplyyear(o.getApplyyear());
                bugetProcureProjectHVO.setFourprono(o.getFourprono());
                bugetProcureProjectHVO.setFourproname(o.getFourproname());
                bugetProcureProjectHVO.setSpec(o.getSpec());
                bugetProcureProjectHVO.setNnum(o.getNnum());
                bugetProcureProjectHVO.setPrice(o.getPrice());
                bugetProcureProjectHVO.setMoney(o.getMoney());
                bugetProcureProjectHVO.setFourstate(o.getFourstate());
                Object attach = o.getAttach();
                if (attach != null ) {
                    bugetProcureProjectHVO.setAttach(attach.toString());
                }
                bugetProcureProjectHVO.setCreationtime(o.getCreationtime());
                bugetProcureProjectHVO.setCreator(o.getCreator());
                bugetProcureProjectHVO.setModifier(o.getModifier());
                bugetProcureProjectHVO.setModifiedtime(o.getModifiedtime());
                bugetProcureProjectHVO.setModifyobj(o.getModifyobj());
                bugetProcureProjectHVO.setDef1(o.getDef1());
                bugetProcureProjectHVO.setDef2(o.getDef2());
                bugetProcureProjectHVO.setDef3(o.getDef3());
                bugetProcureProjectHVO.setDef4(o.getDef4());
                bugetProcureProjectHVO.setDef5(o.getDef5());
                bugetProcureProjectHVO.setDef6(o.getDef6());
                bugetProcureProjectHVO.setDef7(o.getDef7());
                bugetProcureProjectHVO.setDef8(o.getDef8());
                bugetProcureProjectHVO.setDef9(o.getDef9());
                bugetProcureProjectHVO.setDef10(o.getDef10());
                list.add(bugetProcureProjectHVO);
            }
        }
        InputLogger.info(logName, "----end--解析,三方系统: 【" + paramJson.getString("sender") + "】,请求参数 ");
        return list;

    }

    private String buildSQL(List<BugetProcureProjectHVO> list, String businessCode) {
        StringBuilder sql = new StringBuilder();
        if (businessCode.equals(BusinessCodeEnumerate.QUERY.getCode())) {
            // 查询数据
            sql.append("SELECT "  );
            sql.append(" h.pk_cgid, h.cgbillcode, h.applydept, "  );
            sql.append(" h.pldycbks, h.spec, b.ysyear applyyear , "  );
            sql.append(" h.nnum, h.price, h.money, h.hopbillcode, "  );
            sql.append(" h.modifier, h.modifyobj, h.def1, h.hopbillcode fourprono , h.name fourproname, "  );
            sql.append(" b.oneprono,b.oneproname,b.twoprono,b.twoproname, "  );
            sql.append(" b.threeprono,b.threeproname, "  );
//            sql.append(" b.kymoney kymoney,  b.threeprotzmoney threeprotzmoney, b.threeprofinysmoney threeprofinysmoney,b.threeproysmoney threeproysmoney,   "  );
            sql.append(" nvl(b.fourstate, 1) fourstate ,nvl(b.budstate, 2) budstate "  );
            sql.append("FROM HRPBM_SJXMH h "  );
            sql.append("left join HRPBM_SJXMB b on h.pk_sjxm = b.pk_sjxm "  );
            sql.append("WHERE 1 = 1 "  );
//            sql.append(" and b.fourstate not in (5)");
            if (CollectionUtil.isNotEmpty(list)) {
                BugetProcureProjectHVO bppvo = list.get(0);
                if (!StringUtil.isEmpty(bppvo.getPk_cgid()) || !StringUtil.isEmpty(bppvo.getCgbillcode())) {
                    sql.append(" and (h.pk_cgid,h.cgbillcode, b.ysyear ) in(");
                    for (int i = 0; i < list.size(); i++) {
                        BugetProcureProjectHVO bugetProcureProjectHVO = list.get(i);
                        if (i == list.size() - 1) {
                            sql.append("(").append("'").append(bugetProcureProjectHVO.getPk_cgid()).append("',")
                                    .append("'").append(bugetProcureProjectHVO.getCgbillcode()).append("'")
                                    .append("'").append(bugetProcureProjectHVO.getApplyyear()).append("'")
                                    .append(")");
                        } else {
                            sql.append("(").append("'").append(bugetProcureProjectHVO.getPk_cgid()).append("',")
                                    .append("'").append(bugetProcureProjectHVO.getCgbillcode()).append("'")
                                    .append("'").append(bugetProcureProjectHVO.getApplyyear()).append("'")
                                    .append("),");
                        }

                    }
                    sql.append(")");
                }
            }
        } else if (businessCode.equals(BusinessCodeEnumerate.QUERYTHREE.getCode())) {
            sql = new StringBuilder();
            sql.append("SELECT "  );
            sql.append(" b.oneprono,b.oneproname,b.twoprono,b.twoproname, "  );
            sql.append(" b.threeprono,b.threeproname,b.threeproysmoney threeproysmoney, "  );
            sql.append(" b.threeprotzmoney threeprotzmoney, b.threeprofinysmoney threeprofinysmoney,  "  );
            sql.append(" b.kymoney kymoney, nvl(b.fourstate, 1) fourstate ,nvl(b.budstate, 2) budstate "  );
            sql.append("FROM HRPBM_SJXMH h "  );
            sql.append("left join HRPBM_SJXMB b on h.pk_sjxm = b.pk_sjxm "  );
            sql.append("WHERE 1 = 1 "  );
            sql.append(" and  b.fourstate not in ( 1, 5) ");
            if (CollectionUtil.isNotEmpty(list)) {
                BugetProcureProjectHVO bppvo = list.get(0);
                if (!StringUtil.isEmpty(bppvo.getThreeprono()) || !StringUtil.isEmpty(bppvo.getDef1())) {
                    sql.append(" and (b.threeprono,h.def1) in(");
                    for (int i = 0; i < list.size(); i++) {
                        BugetProcureProjectHVO bugetProcureProjectHVO = list.get(i);
                        if (i == list.size() - 1) {
                            sql.append("(").append("'").append(bugetProcureProjectHVO.getThreeprono()).append("',")
                                    .append("'").append(bugetProcureProjectHVO.getDef1()).append("'").append(")");
                        } else {
                            sql.append("(").append("'").append(bugetProcureProjectHVO.getThreeprono()).append("',")
                                    .append("'").append(bugetProcureProjectHVO.getDef1()).append("'").append("),");
                        }

                    }
                    sql.append(")");
                }
            }
        }

        return sql.toString();
    }

    /**
     * 获取资金来源
     *
     * @throws BusinessException
     */
    @SuppressWarnings("unchecked")
    protected List<Map<String, Object>> getFundSource(List<Map<String, Object>> retLists, String businessCode)
            throws BusinessException {
        for (Map<String, Object> map : retLists) {
            Object threeprono = map.get("threeprono");
            Object year = null;
            if (threeprono != null) {
                String sql = this.builFundSource(threeprono);
                List<Map<String, Object>> lists = (List<Map<String, Object>>) this.getBaseDAO().executeQuery(sql,
                        new MapListProcessor());
                JSONObject json = new JSONObject();
                for (Map<String, Object> c : lists) {
                    year = c.get("year");
                    String key = c.get("code") + "_" + c.get("name").toString();
                    json.put(key, c.get("amount"));
                }
                if (businessCode.equals(BusinessCodeEnumerate.QUERYTHREE.getCode())) {
                    map.put("year", year);
                }
                map.put("capitalsource", json);
            }
        }
        return retLists;
    }

    /**
     *
     * @param threeprono 项目申请单单据号
     * @return
     * @throws BusinessException
     */
    protected String builFundSource(Object threeprono) throws BusinessException {
        String sql = " select " +
                "    b.amount amount ,bf.code ,bf.name,h.applyyear year " +
                " from hrppl_pjappl_b b " +
                " inner join bd_fundsource bf on b.capitalsource = bf.pk_fundsource " +
                " left join hrppl_pjappl h on h.pk_pjappl = b.pk_pjappl " +
                " where b.dr = 0 and h.dr = 0 and  h.billno = '" + threeprono + "'";
        return sql;
    }

    @Override
    protected void doDelete(HttpServletRequest req, HttpServletResponse resp) {
        doAction(req, resp);
    }

    @Override
    protected void doGet(HttpServletRequest req, HttpServletResponse resp) {
        doAction(req, resp);
    }

    @Override
    protected void doPost(HttpServletRequest req, HttpServletResponse resp) {
        doAction(req, resp);
    }

    @Override
    protected void doPut(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        doAction(req, resp);
    }

    public ICGProjectToNCProjectService getToNCProjectService() {
        if (null == this.toNCProjectService) {
            this.toNCProjectService = NCLocator.getInstance().lookup(ICGProjectToNCProjectService.class);
        }
        return this.toNCProjectService;
    }

    public BaseDAO getBaseDAO() {
        if (null == baseDAO) {
            baseDAO = new BaseDAO(InvocationInfoProxy.getInstance().getUserDataSource());
        }
        return baseDAO;
    }

    public ISjxmHVOService getSjxmHVOService() {
        if(null == this.sjxmHVOService){
            this.sjxmHVOService = NCLocator.getInstance().lookup(ISjxmHVOService.class);
        }
        return this.sjxmHVOService;
    }
}
